﻿Imports System.Data.OleDb
Public Class QuanLyChiDAO
    Shared conn_str As String = My.Settings.ConnectionString
    Shared Function GetAll() As DataTable
        Dim dt As New DataTable
        Dim query As String = "select MaPhieuChi,ChiPhi.Ten loai_thu_nhap,NganHang.ten TaiKhoan,lap.fullname ten_nguoi_lap,thu.fullname ten_nguoi_chi,Ngay,SoTien,ChiTiet,PhieuChi.MaNganHang,PhieuChi.MaUserChi,PhieuChi.MaUserLap,PhieuChi.MaChiPhi,MONTH(ngay) thangthu,YEAR(ngay) namthu from PhieuChi " & _
            "left join ChiPhi on ChiPhi.MaChiPhi = PhieuChi.MaChiPhi " & _
            "left join NganHang on NganHang.MaNganHang = PhieuChi.MaChiPhi " & _
            "inner join Users lap on lap.MaUser = PhieuChi.MaUserLap " & _
            "inner join Users thu on thu.MaUser = PhieuChi.MaUserChi"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function GetAllByMa(ByVal MaPhieuChi As Integer) As DataTable
        Dim dt As New DataTable
        Dim query As String = "select PhieuChi.*,users.fullname from PhieuChi " & _
            "inner join users on users.MaUser = PhieuChi.MaUserChi " & _
            "where MaPhieuChi = ?"
        Try
            Dim adapter As New OleDbDataAdapter(query, conn_str)
            adapter.SelectCommand.Parameters.Add("@MaPhieuChi", OleDbType.Integer, 11).Value = MaPhieuChi
            adapter.FillSchema(dt, SchemaType.Source)
            adapter.Fill(dt)
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return dt
    End Function
    Shared Function TrungBinh(ByVal MaUser As Integer) As Double
        Dim tb As Double
        Dim query As String = "select avg(sotien) as sotien from " & _
        "(select sum(sotien) as sotien,MaUserChi from PhieuChi where MaUserChi = ? group by MaUserChi,MONTH(Ngay)) as a " & _
        "where MaUserChi = ?"
        Try
            Using conn As New OleDbConnection(conn_str)
                Dim cmd As New OleDbCommand(query, conn)
                cmd.Parameters.AddWithValue("@MaUserChi", MaUser)
                cmd.Parameters.AddWithValue("@MaUserChi", MaUser)
                conn.Open()
                Double.TryParse(cmd.ExecuteScalar().ToString(), tb)
                conn.Close()
            End Using
        Catch Loi As CustomException.MyException
            Loi.Show(query, conn_str)
        End Try
        Return tb
    End Function
End Class
